package org.kernelab.basis.util;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.IOException;
import java.io.Writer;
import java.util.ArrayList;
import java.util.Collection;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ExcelToSQL implements Runnable
{

	public static int	ROWS_PER_SQL	= 100;

	/**
	 * @param args
	 * @throws IOException
	 */
	public static void main(String[] args) throws IOException
	{

	}

	private File						file;

	private Workbook					workBook;

	private Sheet						sheet;

	private Writer						writer;

	private Collection<ActionListener>	progresses;

	public ExcelToSQL()
	{
		this(null);
	}

	public ExcelToSQL(File file)
	{
		this.setProgresses(new ArrayList<ActionListener>());
		if (file != null) {
			this.setFile(file);
		}
	}

	public File getFile()
	{
		return file;
	}

	public Collection<ActionListener> getProgresses()
	{
		return progresses;
	}

	public Sheet getSheet()
	{
		return sheet;
	}

	public Workbook getWorkBook()
	{
		return workBook;
	}

	public Writer getWriter()
	{
		return writer;
	}

	public void parse()
	{
		try {

			boolean first = true;
			for (int row = 0; row < this.getSheet().getRows(); row++) {

				for (ActionListener listener : this.getProgresses()) {
					listener.actionPerformed(new ActionEvent(this, row, ""));
				}

				if (row % ROWS_PER_SQL == 0) {
					this.getWriter().append("INSERT INTO `");
					this.getWriter().append(sheet.getName());
					this.getWriter().append("` VALUES ");
					first = true;
				}

				if (first) {
					first = false;
				} else {
					this.getWriter().append(',');
				}

				this.getWriter().append("(");

				first = true;
				for (Cell cell : this.getSheet().getRow(row)) {

					if (first) {
						first = false;
					} else {
						this.getWriter().append(',');
					}

					this.getWriter().append('\'' + cell.getContents() + '\'');
				}

				this.getWriter().append(")");

				if ((row + 1) % ROWS_PER_SQL == 0) {
					this.getWriter().append(";\n\r");
				}
			}

			if (this.getSheet().getRows() % ROWS_PER_SQL != 0) {
				this.getWriter().append(";\n\r");
			}

			this.getWriter().append('\n');

			this.getWriter().flush();

		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public void run()
	{
		this.parse();
	}

	public void setFile(File file)
	{
		this.file = file;
		try {
			this.setWorkBook(Workbook.getWorkbook(this.file));
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private void setProgresses(Collection<ActionListener> progresses)
	{
		this.progresses = progresses;
	}

	public void setSheet(int index)
	{
		this.setSheet(this.getWorkBook().getSheet(index));
	}

	private void setSheet(Sheet sheet)
	{
		this.sheet = sheet;
	}

	public void setSheet(String name)
	{
		int index = 0;
		for (String n : this.getWorkBook().getSheetNames()) {
			if (name.equals(n)) {
				break;
			}
			index++;
		}
		this.setSheet(index);
	}

	private void setWorkBook(Workbook workBook)
	{
		this.workBook = workBook;
	}

	public void setWriter(Writer writer)
	{
		this.writer = writer;
	}

}
